import sys
import os
import pandas as pd
from pathlib import Path
from PyQt5.QtWidgets import (
    QApplication, QWidget, QPushButton, QLabel,
    QFileDialog, QVBoxLayout, QMessageBox
)

class ExcelToSQLApp(QWidget):
    def __init__(self):
        super().__init__()
        self.init_ui()

    def init_ui(self):
        self.setWindowTitle("Excel 生成 SQL 工具")
        self.setGeometry(300, 300, 400, 200)

        self.label = QLabel("请选择包含 key_org 和 sql_org 的 Excel 文件", self)

        self.button = QPushButton("选择 Excel 并生成 SQL", self)
        self.button.clicked.connect(self.select_file)

        layout = QVBoxLayout()
        layout.addWidget(self.label)
        layout.addWidget(self.button)

        self.setLayout(layout)

    def select_file(self):
        file_path, _ = QFileDialog.getOpenFileName(self, "选择 Excel 文件", "", "Excel 文件 (*.xlsx *.xls)")

        if not file_path:
            return

        try:
            df = pd.read_excel(file_path, engine='openpyxl')
            if 'key_org' not in df.columns or 'sql_org' not in df.columns:
                QMessageBox.critical(self, "错误", "Excel 中必须包含 'key_org' 和 'sql_org' 列")
                return

            # 下载目录路径
            download_dir = Path.home() / "Downloads"
            os.makedirs(download_dir, exist_ok=True)

            for _, row in df.iterrows():
                key = str(row['key_org']).strip()
                sql = str(row['sql_org']).strip()
                sql = sql.replace('\\n', '\n').replace('\\t', '\t')
                filename = f"{key}.sql"
                file_path_out = download_dir / filename
                with open(file_path_out, 'w', encoding='utf-8') as f:
                    f.write(sql)

            QMessageBox.information(self, "完成", f"SQL 文件已生成，保存在：{download_dir}")
        except Exception as e:
            QMessageBox.critical(self, "异常", f"处理文件出错：\n{str(e)}")


if __name__ == '__main__':
    app = QApplication(sys.argv)
    window = ExcelToSQLApp()
    window.show()
    sys.exit(app.exec_())
